package com.shop.prj.dao.Impl;

import com.shop.prj.bean.Book;
import com.shop.prj.dao.BookDao;
import com.shop.prj.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {
	@Override
	public int insert(Book book) {
		String sql = "INSERT INTO `book`(`book_name`,`book_author`,`book_publisher`,`book_price`,`book_type_id`,"
				+ "`book_disc`,`book_discount`,`book_store`,`book_flag`,`book_create_time`) VALUES (?,?,?,?,?,?,?,?,?,?)";
		int result = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		connection = JDBCUtils.getConnection();
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, book.getBookName());
			preparedStatement.setString(2, book.getBookAuthor());
			preparedStatement.setString(3, book.getBookPublisher());
			preparedStatement.setString(4, book.getBookPrice());
			preparedStatement.setInt(5, book.getBookTypeId());
			preparedStatement.setString(6, book.getBookDisc());
			preparedStatement.setDouble(7, book.getBookDiscount());
			preparedStatement.setInt(8, book.getBookStore());
			preparedStatement.setInt(9, book.getBookFlag());
			preparedStatement.setDate(10, book.getBookCreateTime());
			result = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();

			}
		}
		return result;
	}

	@Override
	public int update(Book book) {

		String sql = "UPDATE `Book` SET `book_name`=?,`book_author`=?,`book_publisher`=?,`book_price`=?,"
				+ "`book_type_id`=?,`book_disc`=?,`book_discount`=?,`book_store`=?,`book_flag`=?,`book_create_time`=?WHERE  `book_id`=?";
		int result = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		connection = JDBCUtils.getConnection();
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, book.getBookName());
			preparedStatement.setString(2, book.getBookAuthor());
			preparedStatement.setString(3, book.getBookPublisher());
			preparedStatement.setString(4, book.getBookPrice());
			preparedStatement.setInt(5, book.getBookTypeId());
			preparedStatement.setString(6, book.getBookDisc());
			preparedStatement.setDouble(7, book.getBookDiscount());
			preparedStatement.setInt(8, book.getBookStore());
			preparedStatement.setInt(9, book.getBookFlag());
			preparedStatement.setDate(10, book.getBookCreateTime());
			preparedStatement.setInt(11, book.getBookId());
			result = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();

			}
		}
		return result;
	}

	@Override
	public int delete(Integer id) {
		String sql = "DELETE FROM `Book` WHERE `book_id` = ?";
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement preparedStatement = null;
		int result = 0;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			result = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	@Override
	public Book queryByBookId(Integer id) {
		String sql = "SELECT * FROM `Book` WHERE `Book_id`=?";

		Connection connection = JDBCUtils.getConnection();
		PreparedStatement preparedStatement = null;

		ResultSet resultSet = null;
		Book book = null;

		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setLong(1, id);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				book = new Book();
				book.setBookId(resultSet.getInt(1));
				book.setBookName(resultSet.getString(2));
				book.setBookAuthor(resultSet.getString(3));

				book.setBookPublisher(resultSet.getString(4));
				book.setBookPrice(resultSet.getString(5));
				book.setBookTypeId(resultSet.getInt(6));
				book.setBookDisc(resultSet.getString(7));
				book.setBookDiscount(resultSet.getDouble(8));
				book.setBookStore(resultSet.getInt(9));
				book.setBookFlag(resultSet.getInt(10));
				book.setBookCreateTime(resultSet.getDate(11));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return book;
	}

	@Override
	public List<Book> queryAll() {
		String sql = "SELECT * FROM `Book`";

		Connection connection = JDBCUtils.getConnection();
		Statement statement = null;
		ResultSet resultSet = null;
		List<Book> list = new ArrayList<Book>();
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {

				Book book = new Book();
				book.setBookId(resultSet.getInt(1));
				book.setBookName(resultSet.getString(2));
				book.setBookAuthor(resultSet.getString(3));

				book.setBookPublisher(resultSet.getString(4));
				book.setBookPrice(resultSet.getString(5));
				book.setBookTypeId(resultSet.getInt(6));
				book.setBookDisc(resultSet.getString(7));
				book.setBookDiscount(resultSet.getDouble(8));
				book.setBookStore(resultSet.getInt(9));
				book.setBookFlag(resultSet.getInt(10));
				book.setBookCreateTime(resultSet.getDate(11));
				list.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;

	}

	@Override
	public List<Book> queryByBookName(String bookName) {
		String sql = "SELECT * FROM `Book`WHERE `book_name`LIKE ?";

		Connection connection = JDBCUtils.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Book> list = new ArrayList<Book>();
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "%" + bookName + "%");

			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				Book book = new Book();
				book.setBookId(resultSet.getInt(1));
				book.setBookName(resultSet.getString(2));
				book.setBookAuthor(resultSet.getString(3));
				book.setBookPublisher(resultSet.getString(4));
				book.setBookPrice(resultSet.getString(5));
				book.setBookTypeId(resultSet.getInt(6));
				book.setBookDisc(resultSet.getString(7));
				book.setBookDiscount(resultSet.getDouble(8));
				book.setBookStore(resultSet.getInt(9));
				book.setBookFlag(resultSet.getInt(10));
				book.setBookCreateTime(resultSet.getDate(11));
				list.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public Book queryByBook(String name) {
		String sql = "SELECT * FROM `Book` WHERE `Book_name`=?";

		Connection connection = JDBCUtils.getConnection();
		PreparedStatement preparedStatement = null;

		ResultSet resultSet = null;
		Book book = null;

		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				book = new Book();
				book.setBookId(resultSet.getInt(1));
				book.setBookName(resultSet.getString(2));
				book.setBookAuthor(resultSet.getString(3));

				book.setBookPublisher(resultSet.getString(4));
				book.setBookPrice(resultSet.getString(5));
				book.setBookTypeId(resultSet.getInt(6));
				book.setBookDisc(resultSet.getString(7));
				book.setBookDiscount(resultSet.getDouble(8));
				book.setBookStore(resultSet.getInt(9));
				book.setBookFlag(resultSet.getInt(10));
				book.setBookCreateTime(resultSet.getDate(11));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return book;
	}

	public static void main(String[] args) {
		Book book = new Book();
		book.setBookId(Integer.valueOf("4"));
		book.setBookName("小e");
		book.setBookAuthor("小明");
		book.setBookPublisher("北极");
		book.setBookPrice("14");
		book.setBookTypeId(Integer.valueOf("3"));
		book.setBookDisc("dasd");
		book.setBookDiscount(Double.valueOf("0.9"));
		book.setBookStore(Integer.valueOf("12"));
		book.setBookFlag(Integer.valueOf("21"));
		book.setBookCreateTime(new Date(System.currentTimeMillis()));
		BookDao bookDao = new BookDaoImpl();
		bookDao.update(book);

	}
}
